﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Gasco.DataAccess;
using System.Data.Linq;

namespace Gasco.Business.Services
{
    public class PlattService : IPlattService
    {

        #region IPlattService Members

        public IList<platt> GetAll()
        {
            GascoDBDataContext db = DatabaseHelper.GetData();
            return db.platts.ToList<platt>();
        }

        public int GetCountAll()
        {
            GascoDBDataContext db = DatabaseHelper.GetData();
            return db.platts.Count();
        }

        public int GetCountAll(DateTime startDate, DateTime endDate)
        {
            GascoDBDataContext db = DatabaseHelper.GetData();
            var result = from p in db.platts
                         where p.create_date >= startDate && p.create_date <= endDate
                         select p;

            return result.Count();
        }

        public IList<platt> GetAll(int pageNumber, int pageSize)
        {
            GascoDBDataContext db = DatabaseHelper.GetData();
            return db.platts.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList<platt>();
        }

        public IList<platt> GetAll(int pageNumber, int pageSize, DateTime startDate, DateTime endDate)
        {
            GascoDBDataContext db = DatabaseHelper.GetData();
            var result = from p in db.platts
                         where p.create_date >= startDate && p.create_date <= endDate
                         select p;

            return result.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList<platt>();
        }

        public IList<platt> GetByProduct(int productId)
        {
            GascoDBDataContext db = DatabaseHelper.GetData();
            var result = from p in db.platts
                         where p.product_id == productId
                         select p;

            return result.ToList<platt>();
        }

        public platt GetByProductAndDate(int productId, DateTime plattsDate, int platt_type_id)
        {
            GascoDBDataContext db = DatabaseHelper.GetData();

            var result = from p in db.platts
                         where p.product_id == productId && p.platt_type_id == platt_type_id &&
                               p.create_date.Date <=  plattsDate.Date
                         orderby p.create_date descending
                         select p;

            return result.FirstOrDefault<platt>();
        }

        public float GetAveragePriceByDate(int productId, int plattsTypeId, DateTime startDate, DateTime endDate)
        {
            GascoDBDataContext db = DatabaseHelper.GetData();
            var result = from p in db.platts
                         where p.create_date >= startDate && p.create_date <= endDate
                         select p;

            if (result.Count() == 0)
            {
                platt startPlatt = this.GetByProductAndDate(productId, startDate, plattsTypeId);
                if (startPlatt == null)
                    return 0;
                else
                    return startPlatt.price;
            }

            float totalPrice = result.Sum(p => p.price);
            return (totalPrice / result.Count());
        }

        public platt CheckForDuplicate(int productId, DateTime plattsDate, int plattsType, int plattsUnit)
        {
            GascoDBDataContext db = DatabaseHelper.GetData();

            var result = from p in db.platts
                         where p.product_id == productId &&
                               p.create_date.Date == plattsDate.Date &&
                               p.platt_type_id == plattsType &&
                               p.unit_id == plattsUnit
                         select p;

            return result.FirstOrDefault<platt>();
        }

        public IList<platt> GetByProduct(int productId, int pageNumber, int pageSize)
        {
            GascoDBDataContext db = DatabaseHelper.GetData();
            var result = from p in db.platts
                         where p.product_id == productId
                         select p;

            return result.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList<platt>();
        }

        public IList<platt> GetByProduct(int productId, int pageNumber, int pageSize, DateTime startDate, DateTime endDate)
        {
            GascoDBDataContext db = DatabaseHelper.GetData();
            var result = from p in db.platts
                         where p.product_id == productId && p.create_date >= startDate && p.create_date <= endDate
                         select p;

            return result.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList<platt>();
        }

        public platt GetById(int id)
        {
            using (GascoDBDataContext db = DatabaseHelper.GetData())
            {
                db.DeferredLoadingEnabled = false;

                var result = from p in db.platts
                             where p.id == id
                             select p;

                platt entity = result.Single<platt>();
                entity.SetAsChangeTrackingRoot(true);
                return entity;
            }
        }

        public void Update(platt p)
        {
            platt result = CheckForDuplicate(p.product_id, p.create_date, p.platt_type_id, p.unit_id);
            if ( result != null && p.id != result.id )            
                throw new DuplicatePlattException();

            DatabaseHelper.Update<platt>(p);
        }

        public void Delete(int id)
        {
            DatabaseHelper.Delete<platt>(GetById(id));
        }

        public platt Create(platt p)
        {
            if (CheckForDuplicate(p.product_id, p.create_date, p.platt_type_id, p.unit_id) != null)
                throw new DuplicatePlattException();

            return DatabaseHelper.Insert<platt>(p);
        }

        public IList<platt> SearchPlatts(string keyword, int pageSize, int pageNumber, string sortBy, string sortDirection)
        {
            var db = DatabaseHelper.GetData();
            IQueryable<platt> result = null;

            if (String.IsNullOrEmpty(keyword))
            {
                result = from p in db.platts
                         select p;
            }
            else
            {
                result = from p in db.platts
                         where (p.product.name.Contains(keyword) || p.platts_type.name.Contains(keyword) ||
                                p.currency.name.Contains(keyword))
                         select p;
            }

            if (sortDirection.ToLower() == "descending")
                sortBy = String.Format("{0} DESC",sortBy);

            return result.Skip((pageNumber - 1) * pageSize).Take(pageSize).SortBy<platt>(sortBy).ToList<platt>();
        }

        public int CountSearchPlatts(string keyword)
        {
            var db = DatabaseHelper.GetData();
            IQueryable<platt> result = null;

            if (String.IsNullOrEmpty(keyword))
            {
                result = from p in db.platts
                         select p;
            }
            else
            {
                result = from p in db.platts
                         where (p.product.name.Contains(keyword) || p.platts_type.name.Contains(keyword) ||
                                p.currency.name.Contains(keyword))
                         select p;
            }
            return result.Count();
        }

       public IList<platt> SearchDatePlatts(DateTime fromDate, DateTime toDate, int pageSize, int pageNumber, string sortBy, string sortDirection)
        {
            var db = DatabaseHelper.GetData();
            IQueryable<platt> result = null;

            if (fromDate == null || toDate == null)
            {
                result = from p in db.platts
                         select p;
            }
            else
            {
                result = from p in db.platts
                         where (p.create_date <= toDate && p.create_date >= fromDate)
                         select p;
            }

            if (sortDirection.ToLower() == "descending")
                sortBy = String.Format("{0} DESC", sortBy);

            return result.Skip((pageNumber - 1) * pageSize).Take(pageSize).SortBy<platt>(sortBy).ToList<platt>();
        }

       public int CountSearchDatePlatts(DateTime fromDate, DateTime toDate)
       {
           var db = DatabaseHelper.GetData();
           IQueryable<platt> result = null;

           if (fromDate == null || toDate == null)
           {
               result = from p in db.platts
                        select p;
           }
           else
           {
               result = from p in db.platts
                        where (p.create_date <= toDate && p.create_date >= fromDate)
                        select p;
           }
           return result.Count();
       }

        #endregion
    }
}
